﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml.Serialization;


/// <summary>
/// 
/// 弱一致：list内部元素指针一致
/// 强一致：list指针一致
/// fk的fcolumn与pk/unique虽然是弱一致，但是无法保证强一致（建fk决定了）
/// 由于外键与主键、一位存在弱重复，则对于修改list是必须的，修改datatype是多余的（不怕）
/// 
/// 20070922:添加主键的时候自动修改IsNullable
/// </summary>
namespace Pixysoft.Framework.XSchEmA.Entity
{
    /// <summary>
    /// database schema
    /// </summary>
    public class DatabaseSchema
    {
        private const int DEFAULT_LENGTH_OF_NAME = 30;
        private DatabaseInfo info = new DatabaseInfo();

        private List<TableSchema> tables = new List<TableSchema>();
        private List<UniqueSchema> uniques = new List<UniqueSchema>();
        private List<IndexSchema> indexs = new List<IndexSchema>();
        private List<PrimaryKeySchema> primaryKeys = new List<PrimaryKeySchema>();
        private List<ForeignKeySchema> foreignKeys = new List<ForeignKeySchema>();

        /// <summary>
        /// database configuration
        /// </summary>
        public DatabaseInfo Info
        {
            get { return info; }
            set { info = value; }
        }
        /// <summary>
        /// tables
        /// </summary>
        public List<TableSchema> Tables
        {
            get { return tables; }
            set { tables = value; }
        }
        /// <summary>
        /// unique schema
        /// </summary>
        public List<UniqueSchema> Uniques
        {
            get { return uniques; }
            set { uniques = value; }
        }
        /// <summary>
        /// index schema
        /// </summary>
        public List<IndexSchema> Indexs
        {
            get { return indexs; }
            set { indexs = value; }
        }
        /// <summary>
        /// primary key schema
        /// </summary>
        public List<PrimaryKeySchema> PrimaryKeys
        {
            get { return primaryKeys; }
            set { primaryKeys = value; }
        }
        /// <summary>
        /// foreign key schema
        /// </summary>
        public List<ForeignKeySchema> ForeignKeys
        {
            get { return foreignKeys; }
            set { foreignKeys = value; }
        }

        /// <summary>
        /// database schema
        /// </summary>
        public DatabaseSchema() { }
        /// <summary>
        /// database schema
        /// </summary>
        /// <param name="info"></param>
        public DatabaseSchema(DatabaseInfo info)
        {
            this.info = info;
        }
        /// <summary>
        /// check database
        /// </summary>
        public void Inspection()
        {
            if (tables == null)
                throw new Exception("003001");

            if (primaryKeys == null)
                throw new Exception("003002");

            if (foreignKeys == null)
                throw new Exception("003003");

            foreach (TableSchema table in tables)
                table.Inspection();

            foreach (UniqueSchema unique in uniques)
                unique.Inspection();

            foreach (IndexSchema index in indexs)
                index.Inspection();

            foreach (PrimaryKeySchema pk in primaryKeys)
                pk.Inspection();

            foreach (ForeignKeySchema fk in ForeignKeys)
                fk.Inspection();

            //09-09-26 验证 key的名称是否重复

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (this.GetTable(pk.ConsName) != null)
                    throw new Exception("primary key name is the same as tablename: " + pk.ConsName);

            foreach (UniqueSchema unique in uniques)
                if (this.GetTable(unique.ConsName) != null)
                    throw new Exception("unique key name is the same as tablename: " + unique.ConsName);

            foreach (IndexSchema index in indexs)
                if (this.GetTable(index.ConsName) != null)
                    throw new Exception("index key name is the same as tablename: " + index.ConsName);

            foreach (ForeignKeySchema fk in foreignKeys)
                if (this.GetTable(fk.ConsName) != null)
                    throw new Exception("foreign key name is the same as tablename: " + fk.ConsName);


        }

        /// <summary>
        /// build pointer by name
        /// </summary>
        public void Build()
        {
            BuildColumns();
            BuildUniques();
            BuildIndexs();
            BuildPrimaryKeys();
            BuildForeignKeys();
        }
        /// <summary>
        /// refresh name by pointer
        /// </summary>
        public void Refresh()
        {
            RefreshColumns();
            RefreshUniques();
            RefreshIndexs();
            RefreshPrimaryKeys();
            RefreshForeignKeys();
        }
        /// <summary>
        /// build columns
        /// </summary>
        public void BuildColumns()
        {
            List<TableSchema> removeList = new List<TableSchema>();

            foreach (TableSchema table in tables)
            {
                if (table.Columns.Count == 0)
                {
                    removeList.Add(table);

                    LoggerHelper.Info("Empty Table" + table.TableName);

                    continue;
                }

                table.BuildColumns();
            }

            foreach (TableSchema cons in removeList)
                tables.Remove(cons);


        }
        /// <summary>
        /// referesh columns
        /// </summary>
        public void RefreshColumns()
        {
            foreach (TableSchema table in Tables)
                table.Refresh();
        }
        /// <summary>
        /// build unique
        /// </summary>
        public void BuildUniques()
        {
            List<UniqueSchema> removeList = new List<UniqueSchema>();

            foreach (UniqueSchema uSchema in Uniques)
            {
                string tableName = uSchema.TableName;

                TableSchema table = GetTable(tableName);

                if (table == null && !removeList.Contains(uSchema))
                {
                    removeList.Add(uSchema);
                    LoggerHelper.Info("Table not Exist:" + tableName);
                    continue;
                }

                uSchema.Table = table;

                //10-04-26 先清空在build

                uSchema.Columns.Clear();

                foreach (string columnName in uSchema.ColumnNames)
                {
                    ColumnSchema column = table.GetColumn(columnName);

                    if (column == null && !removeList.Contains(uSchema))
                    {
                        removeList.Add(uSchema);
                        LoggerHelper.Info("Column not Exist:" + columnName);
                        break;
                    }

                    if (!uSchema.Columns.Contains(column))
                        uSchema.Columns.Add(column);
                }
            }

            foreach (UniqueSchema cons in removeList)
                uniques.Remove(cons);

        }
        /// <summary>
        /// refresh unique
        /// </summary>
        public void RefreshUniques()
        {
            foreach (UniqueSchema uSchema in Uniques)
                uSchema.Refresh();
        }
        /// <summary>
        /// build index
        /// </summary>
        public void BuildIndexs()
        {
            List<IndexSchema> removeList = new List<IndexSchema>();

            foreach (IndexSchema iSchema in Indexs)
            {
                string tableName = iSchema.TableName;

                TableSchema table = GetTable(tableName);

                if (table == null && !removeList.Contains(iSchema))
                {
                    removeList.Add(iSchema);
                    LoggerHelper.Info("Table not Exist:" + tableName);
                    continue;
                }

                iSchema.Table = table;


                //10-04-26 先清空在build

                iSchema.Columns.Clear();

                foreach (string columnName in iSchema.ColumnNames)
                {
                    ColumnSchema column = table.GetColumn(columnName);

                    if (column == null && !removeList.Contains(iSchema))
                    {
                        removeList.Add(iSchema);
                        LoggerHelper.Info("Column not Exist:" + columnName);
                        break;
                    }

                    if (!iSchema.Columns.Contains(column))
                        iSchema.Columns.Add(column);
                }
            }

            foreach (IndexSchema cons in removeList)
                indexs.Remove(cons);

        }
        /// <summary>
        /// refresh unique
        /// </summary>
        public void RefreshIndexs()
        {
            foreach (IndexSchema iSchema in Indexs)
                iSchema.Refresh();
        }
        /// <summary>
        /// build primary key
        /// </summary>
        public void BuildPrimaryKeys()
        {
            List<PrimaryKeySchema> removeList = new List<PrimaryKeySchema>();

            foreach (PrimaryKeySchema pkSchema in PrimaryKeys)
            {
                string tableName = pkSchema.TableName;

                TableSchema table = GetTable(tableName);

                if (table == null && !removeList.Contains(pkSchema))
                {
                    removeList.Add(pkSchema);
                    LoggerHelper.Info("Table not Exist:" + tableName);
                    continue;
                }

                pkSchema.Table = table;

                //10-04-26 先清空在build

                pkSchema.Columns.Clear();

                foreach (string columnName in pkSchema.ColumnNames)
                {
                    ColumnSchema column = table.GetColumn(columnName);

                    if (column == null && !removeList.Contains(pkSchema))
                    {
                        removeList.Add(pkSchema);
                        LoggerHelper.Info("Column not Exist:" + columnName);
                        break;
                    }

                    if (!pkSchema.Columns.Contains(column))
                    {
                        column.IsNullable = false;
                        pkSchema.Columns.Add(column);
                    }
                }
            }

            foreach (PrimaryKeySchema cons in removeList)
                primaryKeys.Remove(cons);
        }
        /// <summary>
        /// refresh primary key
        /// </summary>
        public void RefreshPrimaryKeys()
        {
            foreach (PrimaryKeySchema pkSchema in PrimaryKeys)
                pkSchema.Refresh();
        }
        /// <summary>
        /// 根据string重建对象
        /// </summary>
        public void BuildForeignKeys()
        {
            List<ForeignKeySchema> removeList = new List<ForeignKeySchema>();

            foreach (ForeignKeySchema fkSchema in ForeignKeys)
            {
                string fkTableName = fkSchema.FkTableName;

                TableSchema fkTable = GetTable(fkTableName);

                if (fkTable == null && !removeList.Contains(fkSchema))
                {
                    removeList.Add(fkSchema);
                    LoggerHelper.Info("Table not Exist:" + fkTableName);
                    continue;
                }


                fkSchema.FkTable = fkTable;

                //10-04-26 先清空在build

                fkSchema.FkColumns.Clear();

                foreach (string columnName in fkSchema.FkColumnNames)
                {
                    ColumnSchema column = fkTable.GetColumn(columnName);

                    if (column == null && !removeList.Contains(fkSchema))
                    {
                        removeList.Add(fkSchema);
                        LoggerHelper.Info("Column not Exist:" + columnName);
                        break;
                    }

                    if (!fkSchema.FkColumns.Contains(column))
                        fkSchema.FkColumns.Add(column);
                }


                string pkTableName = fkSchema.PkTableName;

                TableSchema pkTable = GetTable(pkTableName);

                if (pkTable == null && !removeList.Contains(fkSchema))
                {
                    removeList.Add(fkSchema);
                    LoggerHelper.Info("Table not Exist:" + pkTableName);
                    continue;
                }


                fkSchema.PkTable = pkTable;

                //10-04-26 先清空在build

                fkSchema.PkColumns.Clear();

                foreach (string columnName in fkSchema.PkColumnNames)
                {
                    ColumnSchema column = pkTable.GetColumn(columnName);

                    if (column == null && !removeList.Contains(fkSchema))
                    {
                        removeList.Add(fkSchema);
                        LoggerHelper.Info("Column not Exist:" + columnName);
                        break;
                    }

                    if (!fkSchema.PkColumns.Contains(column))
                        fkSchema.PkColumns.Add(column);
                }
            }

            foreach (ForeignKeySchema cons in removeList)
                foreignKeys.Remove(cons);
        }
        /// <summary>
        /// 根据对象重建string关系
        /// </summary>
        public void RefreshForeignKeys()
        {
            foreach (ForeignKeySchema fkSchema in ForeignKeys)
                fkSchema.Refresh();
        }


        /// <summary>
        /// get table by name
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public TableSchema GetTable(string tableName)
        {
            if (tableName == null)
                return null;

            foreach (TableSchema table in tables)
                if (table.TableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    return table;

            return null;
        }
        /// <summary>
        /// contain table by name
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool ContainedTable(string tableName)
        {
            if (tableName == null)
                return false;

            foreach (TableSchema table in tables)
                if (table.TableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    return true;

            return false;
        }
        /// <summary>
        /// remove table
        /// </summary>
        /// <param name="table"></param>
        public void RemoveTable(TableSchema table)
        {
            if (table == null)
                return;

            foreach (ForeignKeySchema fk in GetForeignKeys(table))
                ForeignKeys.Remove(fk);

            foreach (ForeignKeySchema fk in GetBeingForeignKeys(table))
                ForeignKeys.Remove(fk);

            PrimaryKeySchema pk = GetPrimaryKey(table);
            if (pk != null)
                PrimaryKeys.Remove(pk);

            foreach (UniqueSchema unique in GetUniques(table))
                Uniques.Remove(unique);

            foreach (IndexSchema index in GetIndexs(table))
                Indexs.Remove(index);

            Tables.Remove(table);

        }
        /// <summary>
        /// remove table by name
        /// </summary>
        /// <param name="tableName"></param>
        public void RemoveTable(string tableName)
        {
            TableSchema table = GetTable(tableName);

            RemoveTable(table);
        }

        /// <summary>
        /// get primary key by pk tablename
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKey(string tableName)
        {
            if (tableName == null)
                return null;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.TableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    return pk;

            return null;
        }
        /// <summary>
        /// get primary key by pk table
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKey(TableSchema table)
        {

            if (table == null)
                return null;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.Table == table)
                    return pk;

            return null;
        }
        /// <summary>
        /// get primary key by tablename and columns
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKey(string tableName, params string[] columns)
        {
            return GetPrimaryKey(tableName, SchemaUtility.ObjectToList<string>(columns));
        }
        /// <summary>
        /// get primary key by tablename and columns
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKey(string tableName, List<string> columns)
        {

            if (tableName == null)
                return null;

            if (columns == null)
                return null;

            TableSchema table = GetTable(tableName);

            if (table == null)
                return null;

            List<ColumnSchema> columnlist = SchemaUtility.StringToSchemaList<ColumnSchema>(columns, table.Columns);

            return GetPrimaryKey(columnlist);
        }
        /// <summary>
        /// get primary key by columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKey(List<ColumnSchema> columns)
        {
            if (columns == null)
                return null;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (SchemaUtility.Equals(columns, pk.Columns))
                    return pk;

            return null;
        }
        /// <summary>
        /// whether table has primary key
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool HasPrimaryKey(TableSchema table)
        {
            return GetPrimaryKey(table) != null;
        }
        /// <summary>
        /// whether columns is primary key
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsPrimaryKey(List<ColumnSchema> columns)
        {
            if (columns == null || columns.Count == 0)
                return false;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (SchemaUtility.Equals(columns, pk.Columns))
                    return true;

            return false;
        }
        /// <summary>
        /// whether column is in one of primary key
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public bool IsPrimaryKey(ColumnSchema column)
        {
            if (column == null)
                return false;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.Columns.Contains(column))
                    return true;

            return false;
        }
        /// <summary>
        /// get primary key by constraint name
        /// </summary>
        /// <param name="pkName"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKeyByName(string pkName)
        {
            if (pkName == null)
                return null;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.ConsName.ToUpper().Trim() == pkName.ToUpper().Trim())
                    return pk;

            return null;
        }
        /// <summary>
        /// get primary key by constraint name
        /// </summary>
        /// <param name="pkName"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetPrimaryKeyByName(string tbname, string pkName)
        {
            if (string.IsNullOrEmpty(tbname))
                return null;

            if (pkName == null)
                return null;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.ConsName.ToUpper().Trim() == pkName.ToUpper().Trim() &&
                    pk.TableName.ToUpper().Trim() == tbname.Trim().ToUpper())
                    return pk;

            return null;
        }
        /// <summary>
        /// contain primary key by constraint name
        /// </summary>
        /// <param name="pkName"></param>
        /// <returns></returns>
        public bool ContainedPrimaryKey(string pkName)
        {
            return GetPrimaryKeyByName(pkName) != null;
        }


        /// <summary>
        /// get unique by tablename
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<UniqueSchema> GetUniques(string tableName)
        {
            List<UniqueSchema> uniquelist = new List<UniqueSchema>();

            if (tableName == null)
                return uniquelist;

            foreach (UniqueSchema unique in uniques)
                if (unique.TableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    uniquelist.Add(unique);

            return uniquelist;
        }
        /// <summary>
        /// get unique by table
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<UniqueSchema> GetUniques(TableSchema table)
        {
            if (table == null)
                return new List<UniqueSchema>();

            return GetUniques(table.TableName);
        }
        /// <summary>
        /// get uniques by column which contain this column
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public List<UniqueSchema> GetUniques(ColumnSchema column)
        {
            List<UniqueSchema> uniquelist = new List<UniqueSchema>();

            if (column == null)
                return uniquelist;

            foreach (UniqueSchema unique in uniques)
                if (unique.Columns.Contains(column) && !uniquelist.Contains(unique))
                    uniquelist.Add(unique);

            return uniquelist;
        }
        /// <summary>
        /// get unique by columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public UniqueSchema GetUnique(List<ColumnSchema> columns)
        {
            if (columns == null || columns.Count == 0)
                return null;

            foreach (UniqueSchema unique in uniques)
                if (SchemaUtility.Equals(columns, unique.Columns))
                    return unique;

            return null;
        }
        /// <summary>
        /// get unique by tablename and columnnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public UniqueSchema GetUnique(string tableName, params string[] columns)
        {
            return GetUnique(tableName, SchemaUtility.ObjectToList<string>(columns));
        }
        /// <summary>
        /// get unique by tablename and columnnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public UniqueSchema GetUnique(string tableName, List<string> columns)
        {
            if (tableName == null)
                return null;

            if (columns == null || columns.Count == 0)
                return null;

            TableSchema table = GetTable(tableName);
            if (table == null)
                return null;

            List<ColumnSchema> columnlist = SchemaUtility.StringToSchemaList<ColumnSchema>(columns, table.Columns);

            return GetUnique(columnlist);
        }
        /// <summary>
        /// whether table has unique constraint
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool HasUnique(TableSchema table)
        {
            if (table == null)
                return false;

            foreach (UniqueSchema unique in uniques)
                if (unique.Table == table)
                    return true;

            return false;
        }
        /// <summary>
        /// 弱一致
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsUnique(List<ColumnSchema> columns)
        {
            return GetUnique(columns) != null;
        }
        /// <summary>
        /// 弱一致
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsUnique(ColumnSchema column)
        {
            if (column == null)
                return false;

            foreach (UniqueSchema unique in uniques)
                if (unique.Columns.Contains(column))
                    return true;

            return false;
        }
        /// <summary>
        /// get unique by constraint name
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public UniqueSchema GetUniqueByName(string constraintName)
        {
            if (constraintName == null)
                return null;

            foreach (UniqueSchema unique in uniques)
                if (unique.ConsName.ToUpper().Trim() == constraintName.ToUpper().Trim())
                    return unique;

            return null;
        }
        /// <summary>
        /// contain unique by constraint name
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public bool ContainedUnique(string constraintName)
        {
            return GetUniqueByName(constraintName) != null;
        }


        /// <summary>
        /// get index by tablename
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<IndexSchema> GetIndexs(string tableName)
        {
            List<IndexSchema> indexlist = new List<IndexSchema>();

            if (tableName == null)
                return indexlist;

            foreach (IndexSchema index in indexs)
                if (index.TableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    indexlist.Add(index);

            return indexlist;
        }
        /// <summary>
        /// get index by table
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<IndexSchema> GetIndexs(TableSchema table)
        {
            if (table == null)
                return new List<IndexSchema>();

            return GetIndexs(table.TableName);
        }
        /// <summary>
        /// get indexs by column which contain this column
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public List<IndexSchema> GetIndexs(ColumnSchema column)
        {
            List<IndexSchema> indexlist = new List<IndexSchema>();

            if (column == null)
                return indexlist;

            foreach (IndexSchema index in indexs)
                if (index.Columns.Contains(column) && !indexlist.Contains(index))
                    indexlist.Add(index);

            return indexlist;
        }
        /// <summary>
        /// get index by columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public IndexSchema GetIndex(List<ColumnSchema> columns)
        {
            if (columns == null || columns.Count == 0)
                return null;

            foreach (IndexSchema index in Indexs)
                if (SchemaUtility.Equals(columns, index.Columns))
                    return index;

            return null;
        }
        /// <summary>
        /// get index by tablename and columnnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public IndexSchema GetIndex(string tableName, params string[] columns)
        {
            return GetIndex(tableName, SchemaUtility.ObjectToList<string>(columns));
        }
        /// <summary>
        /// get index by tablename and columnnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public IndexSchema GetIndex(string tableName, List<string> columns)
        {
            if (tableName == null)
                return null;

            if (columns == null || columns.Count == 0)
                return null;

            TableSchema table = GetTable(tableName);
            if (table == null)
                return null;

            List<ColumnSchema> columnlist = SchemaUtility.StringToSchemaList<ColumnSchema>(columns, table.Columns);

            return GetIndex(columnlist);
        }
        /// <summary>
        /// whether table has index constraint
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool HasIndex(TableSchema table)
        {
            if (table == null)
                return false;

            foreach (IndexSchema index in indexs)
                if (index.Table == table)
                    return true;

            return false;
        }
        /// <summary>
        /// 弱一致
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsIndex(List<ColumnSchema> columns)
        {
            return GetIndex(columns) != null;
        }
        /// <summary>
        /// 弱一致
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsIndex(ColumnSchema column)
        {
            if (column == null)
                return false;

            foreach (IndexSchema index in indexs)
                if (index.Columns.Contains(column))
                    return true;

            return false;
        }
        /// <summary>
        /// get index by constraint name
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public IndexSchema GetIndexByName(string constraintName)
        {
            if (constraintName == null)
                return null;

            foreach (IndexSchema index in indexs)
                if (index.ConsName.ToUpper().Trim() == constraintName.ToUpper().Trim())
                    return index;

            return null;
        }
        /// <summary>
        /// contain index by constraint name
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public bool ContainedIndex(string constraintName)
        {
            return GetIndexByName(constraintName) != null;
        }


        /// <summary>
        /// get foreign keys by tablename
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetForeignKeys(string tableName)
        {
            List<ForeignKeySchema> fkList = new List<ForeignKeySchema>();

            if (tableName == null)
                return fkList;

            foreach (ForeignKeySchema fk in foreignKeys)
                if (fk.FkTableName.ToUpper().Trim() == tableName.ToUpper().Trim())
                    fkList.Add(fk);

            return fkList;
        }
        /// <summary>
        /// get foreign keys by tablename
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetForeignKeys(TableSchema table)
        {
            return GetForeignKeys(table.TableName);
        }
        /// <summary>
        /// get foreign key by column which contain column
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetForeignKeys(ColumnSchema column)
        {
            List<ForeignKeySchema> fklist = new List<ForeignKeySchema>();
            foreach (ForeignKeySchema fk in ForeignKeys)
                if (fk.FkColumns.Contains(column))
                    fklist.Add(fk);
            return fklist;
        }
        /// <summary>
        /// get foreign key by columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public ForeignKeySchema GetForeignKey(List<ColumnSchema> columns)
        {
            if (columns == null || columns.Count == 0)
                return null;

            foreach (ForeignKeySchema fk in foreignKeys)
                if (SchemaUtility.Equals(columns, fk.FkColumns))
                    return fk;

            return null;
        }
        /// <summary>
        /// get foreign key by tablename and columnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public ForeignKeySchema GetForeignKey(string tableName, params string[] columns)
        {
            return GetForeignKey(tableName, SchemaUtility.ObjectToList<string>(columns));
        }
        /// <summary>
        /// get foreign key by tablename and columnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public ForeignKeySchema GetForeignKey(string tableName, List<string> columns)
        {
            if (tableName == null)
                return null;

            if (columns == null || columns.Count == 0)
                return null;

            TableSchema table = GetTable(tableName);
            if (table == null)
                return null;

            List<ColumnSchema> columnlist = SchemaUtility.StringToSchemaList<ColumnSchema>(columns, table.Columns);

            return GetForeignKey(columnlist);
        }
        /// <summary>
        /// get foreign keys which point to this table
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetBeingForeignKeys(TableSchema table)
        {
            List<ForeignKeySchema> fkList = new List<ForeignKeySchema>();

            if (table == null)
                return fkList;

            foreach (ForeignKeySchema fk in foreignKeys)
            {
                if (fk.PkTableName.Equals(table.TableName, StringComparison.OrdinalIgnoreCase))
                    fkList.Add(fk);
            }

            return fkList;
        }
        /// <summary>
        /// get foreign keys which point to the columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetBeingForeignKeys(List<ColumnSchema> columns)
        {
            List<ForeignKeySchema> fkList = new List<ForeignKeySchema>();

            if (columns == null || columns.Count == 0)
                return fkList;

            foreach (ForeignKeySchema fk in foreignKeys)
            {
                if (SchemaUtility.Equals(columns, fk.PkColumns))
                    fkList.Add(fk);
            }

            return fkList;
        }
        /// <summary>
        /// get foreign keys which point to the columns contain this column
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetBeingForeignKeys(ColumnSchema column)
        {
            List<ForeignKeySchema> fkList = new List<ForeignKeySchema>();

            if (column == null)
                return fkList;

            foreach (ForeignKeySchema fk in foreignKeys)
            {
                if (fk.PkColumns.Contains(column))
                    fkList.Add(fk);
            }

            return fkList;
        }
        /// <summary>
        /// whether table has foreign key
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool HasForeignKey(string tablename)
        {
            return GetForeignKeys(tablename).Count != 0;
        }
        /// <summary>
        /// whether table has foreign key
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool HasForeignKey(TableSchema table)
        {
            return GetForeignKeys(table).Count != 0;
        }
        /// <summary>
        /// whether columns is foreign key's foreign columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsForeignKey(List<ColumnSchema> columns)
        {
            return GetForeignKey(columns) != null;
        }
        /// <summary>
        /// whether fkname is foreign key
        /// </summary>
        /// <param name="fkName"></param>
        /// <returns></returns>
        public bool IsForeignKey(string fkName)
        {
            return GetForeignKeyByName(fkName) != null;
        }
        /// <summary>
        /// whether columns is foreign key's primary columns
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public bool IsBeingForeignKey(List<ColumnSchema> columns)
        {
            return GetBeingForeignKeys(columns).Count != 0;
        }
        /// <summary>
        /// get foreign key by name
        /// </summary>
        /// <param name="fkName"></param>
        /// <returns></returns>
        public ForeignKeySchema GetForeignKeyByName(string fkName)
        {
            if (fkName == null)
                return null;

            foreach (ForeignKeySchema fk in foreignKeys)
                if (fk.ConsName.ToUpper().Trim() == fkName.ToUpper().Trim())
                    return fk;

            return null;
        }
        /// <summary>
        /// constain foreign key
        /// </summary>
        /// <param name="fkName"></param>
        /// <returns></returns>
        public bool ContainedForeignKey(string fkName)
        {
            return GetForeignKeyByName(fkName) != null;
        }


        /// <summary>
        /// get constraint by name (pk or unique)
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public IConstraintSchema GetConstraint(string constraintName)
        {
            if (string.IsNullOrEmpty(constraintName))
                return null;

            foreach (PrimaryKeySchema cons in primaryKeys)
                if (cons.ConsName.ToUpper().Trim() == constraintName.ToUpper().Trim())
                    return cons;

            foreach (UniqueSchema cons in uniques)
                if (cons.ConsName.ToUpper().Trim() == constraintName.ToUpper().Trim())
                    return cons;

            foreach (IndexSchema cons in indexs)
                if (cons.ConsName.ToUpper().Trim() == constraintName.ToUpper().Trim())
                    return cons;

            return null;
        }
        /// <summary>
        /// get constraint by tablename and columnnames
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public IConstraintSchema GetConstraint(string tableName, List<string> columns)
        {
            if (columns == null || columns.Count == 0)
                return null;

            if (tableName == null)
                return null;

            foreach (UniqueSchema unique in uniques)
                if (unique.TableName.ToUpper().Trim() == tableName.ToUpper().Trim() && SchemaUtility.Equals(unique.ColumnNames, columns))
                    return unique;

            foreach (PrimaryKeySchema pk in primaryKeys)
                if (pk.TableName.ToUpper().Trim() == tableName.ToUpper().Trim() && SchemaUtility.Equals(pk.ColumnNames, columns))
                    return pk;

            foreach (IndexSchema index in indexs)
                if (index.TableName.ToUpper().Trim() == tableName.ToUpper().Trim() && SchemaUtility.Equals(index.ColumnNames, columns))
                    return index;

            return null;
        }
        /// <summary>
        /// whether constraintname is a constraint
        /// </summary>
        /// <param name="constraintName"></param>
        /// <returns></returns>
        public bool HasConstraint(string constraintName)
        {
            if (string.IsNullOrEmpty(constraintName))
                return false;

            return GetConstraint(constraintName) != null;
        }

        /// <summary>
        /// 根据列集合模式，取得外键相连的所有外键关系，无弱重复
        /// 适用于查看外键关系
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public List<ForeignKeySchema> GetForeignKeyChain(List<ColumnSchema> columns)
        {
            //不能使用一次遍历，因为可能当前忽略的外键实际上后面发现是相连的

            List<ForeignKeySchema> fkList = new List<ForeignKeySchema>();

            if (columns == null || columns.Count == 0)
                return fkList;

            GetForeignKeyChain(fkList, columns);
            return fkList;

        }
        private void GetForeignKeyChain(List<ForeignKeySchema> fkList, List<ColumnSchema> columns)
        {
            foreach (ForeignKeySchema fk in ForeignKeys)
            {
                if (SchemaUtility.Equals(fk.FkColumns, columns) && !fkList.Contains(fk))//is
                {
                    fkList.Add(fk);

                    GetForeignKeyChain(fkList, fk.PkColumns);
                }

                if (SchemaUtility.Equals(fk.PkColumns, columns) && !fkList.Contains(fk))//being
                {
                    fkList.Add(fk);
                    GetForeignKeyChain(fkList, fk.FkColumns);
                }
            }
            return;
        }

        /// <summary>
        /// 取得外键相连的所有列集合，无弱重复
        /// 适用于修改datatype    
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public List<ColumnSchema> GetStrongForeignColumnChain(ColumnSchema column)
        {
            List<ColumnSchema> columnList = new List<ColumnSchema>();

            columnList.Add(column);

            if (column == null)
                return columnList;

            GetStrongForeignColumnChain(columnList, column);

            return columnList;
        }
        private void GetStrongForeignColumnChain(List<ColumnSchema> columnList, ColumnSchema column)
        {
            foreach (ForeignKeySchema fk in ForeignKeys)
            {
                if (fk.FkColumns.Contains(column) && !columnList.Contains(fk.PkColumns[fk.FkColumns.IndexOf(column)]))//is
                {
                    int columnIndex = fk.FkColumns.IndexOf(column);

                    columnList.Add(fk.PkColumns[columnIndex]);

                    GetStrongForeignColumnChain(columnList, fk.PkColumns[columnIndex]);
                }

                if (fk.PkColumns.Contains(column) && !columnList.Contains(fk.FkColumns[fk.PkColumns.IndexOf(column)]))//being
                {
                    int index = fk.PkColumns.IndexOf(column);

                    columnList.Add(fk.FkColumns[index]);

                    GetStrongForeignColumnChain(columnList, fk.FkColumns[index]);
                }
            }
            return;
        }

        /// <summary>
        /// 根据列集合模式，取得被引用关系的所有外键集合，无弱重复
        /// 适用于检查有无外键 循环
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<TableSchema> GetBeingForeignTableChain(TableSchema table)
        {
            List<TableSchema> tableList = new List<TableSchema>();

            if (table == null)
                return tableList;

            GetBeingForeignTableChain(tableList, table);

            return tableList;
        }
        private void GetBeingForeignTableChain(List<TableSchema> tableList, TableSchema table)
        {
            foreach (ForeignKeySchema fk in ForeignKeys)
            {
                if (fk.PkTable == table && !tableList.Contains(fk.FkTable))//being
                {
                    tableList.Add(fk.FkTable);
                    GetBeingForeignTableChain(tableList, fk.FkTable);
                }
            }
        }

        /// <summary>
        /// 取得外键相连的所有表集合，无弱重复(包括引用与被引用)
        /// 适用于查看表是否有唯一datatype
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<TableSchema> GetForeignTableChain(TableSchema table)
        {
            List<TableSchema> tableList = new List<TableSchema>();

            if (table == null)
                return tableList;

            tableList.Add(table);
            GetForeignTableChain(tableList, table);
            return tableList;
        }
        private void GetForeignTableChain(List<TableSchema> tableList, TableSchema table)
        {
            foreach (ForeignKeySchema fk in ForeignKeys)
            {
                if (fk.FkTable == table && !tableList.Contains(fk.PkTable))//is
                {
                    tableList.Add(fk.PkTable);
                    GetForeignTableChain(tableList, fk.PkTable);
                }

                if (fk.PkTable == table && !tableList.Contains(fk.FkTable))//being
                {
                    tableList.Add(fk.FkTable);
                    GetForeignTableChain(tableList, fk.FkTable);
                }
            }
            return;
        }


        /// <summary>
        /// get default unique name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string GetDefaultUniqueName(string tbName)
        {
            return GetDefaultConstraintName(tbName, "_U");
        }
        /// <summary>
        /// get default index name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string GetDefaultIndexName(string tbName)
        {
            return GetDefaultConstraintName(tbName, "_I");
        }
        /// <summary>
        /// get default primary key name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string GetDefaultPrimaryKeyName(string tbName)
        {
            return GetDefaultConstraintName(tbName, "_PK");
        }
        /// <summary>
        /// get default foreign key name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string GetDefaultForeignKeyName(string tbName)
        {
            return GetDefaultConstraintName(tbName, "_FK");
        }
        /// <summary>
        /// get default constraint name
        /// </summary>
        /// <param name="table"></param>
        /// <param name="symbol"></param>
        /// <returns></returns>
        private string GetDefaultConstraintName(string tableName, string symbol)
        {
            if (tableName == null)
                return null;

            string indexName = tableName + symbol;

            int counter = 0;
            bool conflict = false;

            do
            {
                conflict = false;

                if (indexName.Length + counter.ToString().Length > DEFAULT_LENGTH_OF_NAME)
                    indexName = indexName.Substring(0, DEFAULT_LENGTH_OF_NAME - counter.ToString().Length);


                if (ContainedUnique(indexName + counter.ToString()))
                {
                    counter++;
                    conflict = true;
                    continue;
                }

                if (ContainedIndex(indexName + counter.ToString()))
                {
                    counter++;
                    conflict = true;
                    continue;
                }

                if (ContainedPrimaryKey(indexName + counter.ToString()))
                {
                    counter++;
                    conflict = true;
                    continue;
                }

                if (ContainedForeignKey(indexName + counter.ToString()))
                {
                    counter++;
                    conflict = true;
                    continue;
                }

            }
            while (conflict);

            return indexName + counter.ToString();
        }
        /// <summary>
        /// get default column name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string GetDefaultColumnName(TableSchema table)
        {
            if (table == null)
                return null;

            string columnName = "Column";
            int counter = 0;
            while (table.GetColumn(columnName + counter.ToString()) != null)
                counter++;
            return columnName + counter.ToString();
        }
        /// <summary>
        /// clone
        /// </summary>
        /// <returns></returns>
        public DatabaseSchema Clone()
        {
            DatabaseSchema cloneschema = Pixysoft.IO.ObjectCloner.Clone(this) as DatabaseSchema;
            cloneschema.BuildColumns();
            cloneschema.BuildPrimaryKeys();
            cloneschema.BuildUniques();
            cloneschema.BuildIndexs();
            cloneschema.BuildForeignKeys();
            return cloneschema;
        }
        /// <summary>
        /// to string
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DB Info:" + info.DatabaseType);
            builder.Append("; \t");
            builder.Append("Tables:" + tables.Count);
            builder.Append("; \t");
            builder.Append("Pks:" + primaryKeys.Count);
            builder.Append("; \t");
            builder.Append("Fks:" + foreignKeys.Count);
            builder.Append("; \t");
            builder.Append("Uniques:" + uniques.Count);
            builder.Append("; \t");
            builder.Append("Indexs:" + indexs.Count);
            return builder.ToString();
        }


        /// <summary>
        /// 创建一个新的表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public TableSchema NewTable(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
                throw new Exception("missing talename");

            TableSchema table = new TableSchema(tableName);

            this.tables.Add(table);

            return table;
        }

        /// <summary>
        /// 新建一个表主键
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="columnnames"></param>
        /// <returns></returns>
        public PrimaryKeySchema NewPrimaryKey(string tablename, params string[] columnnames)
        {
            if (string.IsNullOrEmpty(tablename))
                throw new Exception("missing tablename");

            if (columnnames == null || columnnames.Length == 0)
                throw new Exception("missing column names");

            PrimaryKeySchema pk = new PrimaryKeySchema(GetDefaultPrimaryKeyName(tablename), tablename, columnnames);

            this.primaryKeys.Add(pk);

            this.BuildPrimaryKeys();

            return pk;
        }

        /// <summary>
        /// 新建一个外键,直接引用对方主键,我引用了他人的表，则对方是pkTableName,我是fkTableName
        /// </summary>
        /// <returns></returns>
        public ForeignKeySchema NewForeignKey(string pkTableName, string fkTableName, params  string[] fkColNames)
        {
            if (string.IsNullOrEmpty(pkTableName))
                throw new Exception("missing pkTableName");

            if (string.IsNullOrEmpty(fkTableName))
                throw new Exception("missing fkTableName");

            if (fkColNames == null || fkColNames.Length == 0)
                throw new Exception("missing fkColNames");

            IConstraintSchema constriant = GetPrimaryKey(pkTableName);

            if (constriant == null)
                throw new Exception("missing constraint for " + pkTableName);

            ForeignKeySchema fk = new ForeignKeySchema(GetDefaultForeignKeyName(fkTableName));

            fk.PkTableName = pkTableName;
            fk.FkTableName = fkTableName;

            foreach (string fkColName in fkColNames)
                fk.FkColumnNames.Add(fkColName);

            foreach (string pkColName in constriant.ColumnNames)
                fk.PkColumnNames.Add(pkColName);

            this.foreignKeys.Add(fk);

            this.BuildForeignKeys();

            return fk;
        }

        /// <summary>
        /// 新建一个唯一约束
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="columnnames"></param>
        /// <returns></returns>
        public UniqueSchema NewUnique(string tablename, params string[] columnnames)
        {
            if (string.IsNullOrEmpty(tablename))
                throw new Exception("missing tablename");

            if (columnnames == null || columnnames.Length == 0)
                throw new Exception("missing column names");

            string consname = GetDefaultUniqueName(tablename);

            UniqueSchema u = new UniqueSchema(consname, tablename, columnnames);

            this.uniques.Add(u);

            this.BuildUniques();

            return u;
        }

        /// <summary>
        /// 新建一个索引
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="columnnames"></param>
        /// <returns></returns>
        public IndexSchema NewIndex(string tablename, params string[] columnnames)
        {
            if (string.IsNullOrEmpty(tablename))
                throw new Exception("missing tablename");

            if (columnnames == null || columnnames.Length == 0)
                throw new Exception("missing column names");

            string consname = GetDefaultIndexName(tablename);

            IndexSchema i = new IndexSchema(consname, tablename, columnnames);

            this.indexs.Add(i);

            this.BuildIndexs();

            return i;
        }
    }
}
